Configuring Data Sources

On Windows, data sources are configured and modified through the ODBC Administrator.

To configure a Sybase data source:

  1. Start the ODBC Administrator to display a list of data sources.
  2. If you are configuring an existing data source, select the data source name and click Configure to display the ODBC Sybase Wire Protocol Driver Setup dialog box.

    3. If you are configuring a new data source, click Add to display a list of installed drivers. Select the Sybase Wire Protocol driver and click Finish to display the ODBC Sybase Wire Protocol Driver Setup dialog box.

    NOTE: The General tab displays the only fields that are required for creating a data source. The fields on all other tabs are optional, unless noted otherwise.

  3. On the General tab, provide the following information; then, click Apply.

    4. Data Source Name: Type a string that identifies this Sybase data source configuration in the system information. Examples include "Accounting" or "Sys11-Serv1."

    Description: Type an optional long description of a data source name. For example, "My Accounting Database" or "System 11 on Server number 1."

    Network Library Name: Select the name of the network library. This specifies which network protocol to use. The values are Winsock and NamedPipes. The default is Winsock.

    Network Address: Type the network address. The value you specify depends on which network protocol is chosen under Network Library Name and on the Sybase server. If you have chosen Winsock for the Network Library Name, specify an IP address as follows: "<servername or IP address>, <port number>". For example, if your network supports named servers, you may specify an address such as "Sybaseserver, 5000". You may also specify the IP address directly such as "199.226.224.34, 5000".

    If you have chosen NamedPipes as the network protocol, you must specify the pipe address of the server. For example, "\\machine1\sybase\pipe\query".

    Database Name: Type the name of the database to which you want to connect by default. If you do not specify a value, the default is the database defined by the system administrator for each user.

    HA Failover Server Connection Information/Network Address: Type the network address of the High Availability (HA) Failover server to be used in the event of a connection loss. The driver detects the dropped connection and automatically reconnects to the HA Failover server specified by this attribute. This option is valid only for Sybase version 12 or higher servers that have the High Availability Failover feature enabled.

    See the previous description of the Network Address option for an explanation of valid values.

    Interfaces File: Type the path name of the Interfaces file. If you do not specify a value in the Interfaces File field, but do specify a value in the Server Name field, then the driver looks for the path name of the Interfaces file in the Registry under HKEY_LOCAL_MACHINE\SOFTWARE\DataDirect\InterfacesFile. If this Registry value is empty, then the driver attempts to open the SQL.INI file found in the same directory as the driver and use it as the Interfaces file.

    Server Name: Type the name of the section in the Interfaces file that contains the network connection information for the Sybase server you want to access. The section name typically is the host name of the server that contains the Sybase server you want to access.

    Use Sybase LDAP Schema: Select this check box to use an LDAP server to retrieve connection information. Then, complete the next two required connection options. See "Using LDAP" for information about the implementation of LDAP in the Sybase Wire Protocol driver.

    Sybase LDAP URL: Type the Sybase LDAP URL that specifies the search operation that will be used to retrieve information from an LDAP directory. This URL includes host, port, database, distinguished name, and password. The format of this LDAP URL must adhere to RFC 2255.

    Sybase Server Name: Type the unique identifier for the LDAP directory entry where connection information is defined.

    Use LDAP HA Failover Server: Select this check box to use the High Availability (HA) Failover server specified in the Sybase Server Name entry on the LDAP server.

  4. Optionally, click the Advanced tab to specify data source settings.

    5. On this tab, provide any of the following optional information; then, click Apply.

    Cursor Positioning for raiserror: Select a value of 0 or 1 that specifies when the error is returned and where the cursor is positioned when raiserror is encountered.

    When set to 0 (the default), raiserror is handled separately from surrounding statements. The error is returned when raiserror is processed via SQLExecute, SQLExecDirect, or SQLMoreResults. The result set is empty.

    When set to 1 (MS compatible), raiserror is handled with the next statement. The error is returned when the next statement is processed; the cursor is positioned on the first row of the subsequent result set. This could result in multiple raiserrors being returned on a single execute.

    Default Buffer Size for Long Columns (in Kb): Type an integer value that specifies the maximum length of data fetched from a TEXT or IMAGE column. The value must be in multiples of 1024 (for example, 1024, 2048). The default is 1024 KB. You will need to increase this value if the total size of any long data exceeds 1 MB.

    Distributed Transaction Model: Select a model to use for distributed transaction support—either XA Protocol or Native OLE.

    XA Open String Parameters: Type -Ltrace_filename, where trace_filename specifies the name of two trace files that will be created. The first trace file will trace all XA call activities and will be named exactly as you specified. The second trace file will contain tracing of any enlistment and unenlistment procedures and will be named as you specified with a "driver" extension. For example, if you specify XAtrace as the file name, the driver will create two trace files—XAtrace and XAtrace.driver.

    Initialization String: Enter a semicolon-separated list of Sybase language commands that will be issued immediately after connection.

    Enable Quoted Identifiers: Select this check box to allow support of quoted identifiers.

    Enable Describe Parameter: Select this check box to enable the SQLDescribeParam function, which allows an application to describe parameters in SQL statements and in stored procedure calls. To use this option, Prepare Method must be set to 0 or 1, and the SQL statement must not include long parameters. This option should be selected when using Microsoft Remote Data Objects (RDO) to access data.

    Application Using Threads: Select this check box to ensure that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Turning off this setting avoids additional processing required for ODBC thread-safety standards.

    Tightly Coupled Distributed Transactions: Select this check box to use tightly coupled distributed transactions when connected to a Sybase version 12 or higher server and to ensure that multiple connections within the same distributed transaction do not obey each other’s locks.

    When this check box is not selected, the overall performance of the driver is better, but multiple connections within the same distributed transaction may hang each other because the connections do not obey each other’s locks.

    This option is valid only when the driver is enlisted in a distributed transaction and when it is connected to a Sybase version 12 or higher database. Otherwise, this option is ignored.

    Translate: Click Translate to display the Select Translator dialog box, which lists the translators specified in the ODBC Translators section of the system information. Sybase provides a translator named "OEM to ANSI" that translates your data from the IBM PC character set to the ANSI character set.

    Select a translator; then, click OK to close this dialog box and perform the translation.

  5. Optionally, click the Connection tab to specify data source settings.

    6. On this tab, provide any of the following optional information; then, click Apply.

    Database List: Type the databases that appear in the logon dialog box. Separate the names with commas.

    Default Logon ID: Type the default logon ID used to connect to your Sybase database. This ID is case-sensitive. A logon ID is required only if security is enabled for the database you are connecting to. Your ODBC application may override this value or you can override this value in the logon dialog box or connection string.

    Workstation ID: Type the workstation ID used by the client.

    Application Name: Type the name used by Sybase to identify your application.

    Charset: Type the name of a character set. This character set must be installed on the Sybase server. The default is the setting on the Sybase server. For this driver to support Unicode, this attribute must be set to UTF-8. Refer to the Sybase server documentation for a list of valid character set names.

    Language: Type the national language. This language must be installed on the Sybase server. The default is English.

    Use SSL: Select this check box to use a secure socket layer (SSL) connection. SSL is an industry standard for sending encrypted data over client-to-server and server-to-server connections. Connect ODBC supports the IETF TLS (Transport Layer Security) 1.0 standard.

    Trusted Roots File Name: Type the location and name of the trusted roots file. A trusted roots file contains a list of known and trusted CAs (certificate authorities). This file is similar in format to a certificate file, except it contains certificates for CAs known to the entity (client applications, servers, etc.). This file is maintained using a standard ASCII text editor.

    Password Encryption: Select this check box to determine whether password encryption can be performed from the ODBC driver to the server. By default, this check box is not selected.

  6. Optionally, click the Performance tab to specify performance data source settings.

    7. On this tab, provide any of the following optional information; then, click Apply.

    Select Method: Select a value of 0 or 1 that determines whether database cursors are used for Select statements. When set to 0, the default, database cursors are used; when set to 1, Select statements are run directly without using database cursors. A setting of 1 limits the data source to one active statement.

    Prepare Method: Select a value of 0, 1, 2, or 3 that determines whether stored procedures are created on the server for calls to SQLPrepare.

    When set to 0, stored procedures are created for every call to SQLPrepare. This setting can result in decreased performance when processing statements that do not contain parameters.

    When set to 1 (the initial default), the driver creates stored procedures only if the statement contains parameters. Otherwise, the statement is cached and run directly at the time of SQLExecute.

    When set to 2, stored procedures are never created. The driver caches the statement, executes it directly at the time of SQLExecute, and reports any syntax or similar errors at the time of SQLExecute.

    When set to 3, stored procedures are never created. This is identical to value 2 except that any syntax or similar errors are returned at the time of SQLPrepare instead of SQLExecute. Use this setting only if you must have syntax errors reported at the time of SQLPrepare.

    Fetch Array Size: Type the number of rows the driver retrieves when fetching from the server. This is not the number of rows given to the user. The default is 50 rows.

    Packet Size: Type a value of -1, 0, or x that determines the number of bytes per network packet transferred from the database server to the client. The correct setting of this attribute can improve performance.

    When set to -1, the driver computes the maximum allowable packet size on the first connect to the data source and saves the value in the system information.

    When set to 0, the default, the driver uses the default packet size as specified in the Sybase server configuration.

    When set to x, an integer from 1 to 1024, the driver uses a packet size represented by x times 512 bytes. For example, "6" means to set the packet size to 6 * 512 bytes (3072 bytes).

    To take advantage of this connection attribute, you must configure the Sybase server for a maximum network packet size greater than or equal to the value you specified for PacketSize. For example:

    sp_configure "maximum network packet size", 5120

    reconfigure

    Restart Sybase Server

    NOTE: The ODBC specification identifies a connect option, SQL_PACKET_SIZE, that offers this same functionality. To avoid conflicts with applications that may set both the connection string attribute and the ODBC connect option, they have been defined as mutually exclusive. If PacketSize is specified, you will receive a message "Driver Not Capable" if you attempt to call SQL_PACKET_SIZE. If you do not set PacketSize, then application calls to SQL_PACKET_SIZE are accepted by the driver.

    Connection Cache Size: Type a value that determines the number of connections that the connection cache can hold. The default Connection Cache setting is 1. To set the connection cache, you must set the Select Method option to 1 - Direct. Increasing the connection cache may increase performance of some applications but requires additional database resources.

  7. At any point during the configuration process, you can click Test Connect to attempt to connect to the data source using the connection properties specified in the Driver Setup dialog box. A logon dialog box is displayed; see "Connecting to a Data Source Using a Logon Dialog Box" for details. Note that the information you enter in the logon dialog box during a test connect is not saved.
    • If the driver can connect, it releases the connection and displays a "connection established" message. Click OK.
    • If the driver cannot connect because of an improper environment or incorrect connection value, it will display an appropriate error message. Click OK.
  8. Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.